﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using WS1.Models;

namespace WS1.Repository
{
	public class OrdersRepository:IDisposable
	{
        private readonly SqlConnection _sqlConnection;
        public OrdersRepository()
        {
            string connStr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
            _sqlConnection = new SqlConnection(connStr);
            _sqlConnection.Open();
        }
        public IEnumerable<Orders> All()
        {
            var sql = @"SELECT  [transaction_id]
      ,[customer_id]
      ,[Orderdate]
      ,[time]
      ,[No_column_name]
      ,[product_id]
      ,[quantity]
      ,[price]
      ,[payment_method]
      ,[channel]
      ,[store_id]
      ,[promotion_id]
      ,[discount_amount]
  FROM [BelleCroissantLyormais].[dbo].[Orders]
";
            var products = new List<Orders>();
            using (var cmd = new SqlCommand(sql, _sqlConnection))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var product = new Orders()
                        {
                            transaction_id = reader.GetInt32(0),
                            customer_id = reader.GetInt32(1),
                            Orderdate = reader.GetDateTime(2),
                            time = reader.GetTimeSpan(3),
                            No_column_name = reader.GetTimeSpan(4),
                            product_id = reader.GetInt32(5),
                            quantity = reader.GetByte(6),
                            price = reader.GetDouble(7),
                            payment_method = reader.GetString(8),
                            channel = reader.IsDBNull(9) ? string.Empty : reader.GetString(9),

                            store_id = reader.IsDBNull(10) ? (byte?)null : reader.GetByte(10),
                            promotion_id = reader.IsDBNull(11) ? (int?)null : reader.GetInt32(11),
                            discount_amount = reader.IsDBNull(12) ? (decimal?)null : reader.GetDecimal(12)
                        };
                        products.Add(product);
                    }
                }
            }
            return products;
        }

        public Orders Single(int id)
        {
            var sql = @"SELECT [transaction_id]
      ,[customer_id]
      ,[Orderdate]
      ,[time]
      ,[No_column_name]
      ,[product_id]
      ,[quantity]
      ,[price]
      ,[payment_method]
      ,[channel]
      ,[store_id]
      ,[promotion_id]
      ,[discount_amount]
  FROM [BelleCroissantLyormais].[dbo].[Orders]
where [transaction_id]=transaction_id
";
            Orders product = null;
            using (var cmd = new SqlCommand(sql, _sqlConnection))
            {
                cmd.Parameters.AddWithValue("@transaction_id", id);

                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        product = new Orders()
                        {
                            transaction_id = reader.GetInt32(0),
                            customer_id = reader.GetInt32(1),
                            Orderdate =  reader.GetDateTime(2),
                            time = reader.GetTimeSpan(3),
                            No_column_name = reader.GetTimeSpan(4),
                            product_id = reader.GetInt32(5),
                            quantity = reader.GetByte(6),
                            price = reader.GetDouble(7),
                            payment_method = reader.GetString(8),
                            channel = reader.IsDBNull(9) ? string.Empty : reader.GetString(9),

                            store_id = reader.IsDBNull(10) ? (byte?)null : reader.GetByte(10),
                            promotion_id = reader.IsDBNull(11) ? (int?)null : reader.GetInt32(11),
                            discount_amount = reader.IsDBNull(12) ? (decimal?)null : reader.GetDecimal(12)

                        };
                    }
                }

            }
            return product;
        }
        public bool Add(Orders orders)
        {
            //product_id 主键。修改成自增
            var sql = @"INSERT INTO [dbo].[Orders]
           ([customer_id]
           ,[Orderdate]
           ,[time]
           ,[No_column_name]
           ,[product_id]
           ,[quantity]
           ,[price]
           ,[payment_method]
           ,[channel]
           ,[store_id]
           ,[promotion_id]
           ,[discount_amount])
     VALUES
           (@customer_id
           ,@Orderdate
           ,@time
           ,@No_column_name
           ,@product_id
           ,@quantity
           ,@price
           ,@payment_method
           ,@channel
           ,@store_id
           ,@promotion_id
           ,@discount_amount)";
            using (var cmd = new SqlCommand(sql, _sqlConnection))
            {
                cmd.Parameters.AddWithValue("@customer_id", orders.customer_id);
                cmd.Parameters.AddWithValue("@Orderdate", orders.Orderdate);
                cmd.Parameters.AddWithValue("@time", orders.time);
                cmd.Parameters.AddWithValue("@No_column_name", orders.No_column_name);
                cmd.Parameters.AddWithValue("@product_id", orders.product_id);
                cmd.Parameters.AddWithValue("@quantity", orders.quantity);
                cmd.Parameters.AddWithValue("@price", orders.price);
                cmd.Parameters.AddWithValue("@payment_method", orders.payment_method);
                cmd.Parameters.AddWithValue("@channel", orders.channel);
                cmd.Parameters.AddWithValue("@store_id", orders.store_id);
                cmd.Parameters.AddWithValue("@promotion_id", orders.promotion_id);
                cmd.Parameters.AddWithValue("@discount_amount", orders.discount_amount);





                return cmd.ExecuteNonQuery() == 1;
            }
        }

        public bool Update(int id, Orders product)
        {
            var sql = @"UPDATE [dbo].[Orders]
   SET 				
           [customer_id]=@customer_id		
          ,[Orderdate]=@Orderdate		
          ,[time]=@time				
          ,[No_column_name]	=@No_column_name	
          ,[product_id]=@product_id 		
          ,[quantity]=@quantity 		
          ,[price]=@price			
          ,[payment_method]	=@payment_method 	
          ,[channel]=@channel		
          ,[store_id]=@store_id			
          ,[promotion_id]=@promotion_id 		
          ,[discount_amount]	=@discount_amount 	
	  where [transaction_id] =@transaction_id";
            using (var cmd = new SqlCommand(sql, _sqlConnection))
            {
                cmd.Parameters.AddWithValue("@transaction_id", id);
                cmd.Parameters.AddWithValue("@customer_id", product.customer_id);
                cmd.Parameters.AddWithValue("@Orderdate", product.Orderdate);
                cmd.Parameters.AddWithValue("@time", product.time);
                cmd.Parameters.AddWithValue("@No_column_name", product.No_column_name);
                cmd.Parameters.AddWithValue("@product_id", product.product_id);
                cmd.Parameters.AddWithValue("@quantity", product.quantity);
                cmd.Parameters.AddWithValue("@price", product.price);
                cmd.Parameters.AddWithValue("@payment_method", product.payment_method);
                cmd.Parameters.AddWithValue("@channel", product.channel);
                cmd.Parameters.AddWithValue("@store_id", product.store_id);
                cmd.Parameters.AddWithValue("@promotion_id", product.promotion_id);
                cmd.Parameters.AddWithValue("@discount_amount", product.discount_amount);


                return cmd.ExecuteNonQuery() == 1;
            }
        }

        public bool RemoveSingle(int id)
        {
            var sql = @"DELETE FROM [dbo].[Orders]
      WHERE [transaction_id]=@transaction_id";
            using (var cmd = new SqlCommand(sql, _sqlConnection))
            {
                cmd.Parameters.AddWithValue("@transaction_id", id);
                return cmd.ExecuteNonQuery() == 1;
            }
        }
        public void Dispose()
        {

        }
    }
}